<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<html><head>
<title>Selecting and Editing Data in a DataList Control</title>
</head>
<body bgcolor="#ffffff">
<span class="heading">Selecting and Editing Data in a DataList Control</span><hr />
<div id="outError" runat="server" />
<ASP:Label id="lblSQL" runat="server" /><p />
<form runat="server">
  <ASP:DataList id="MyDataList" runat="server"
       CellSpacing = "2"
       SelectedItemStyle-BackColor="red"
       SelectedItemStyle-ForeColor="white"
       EditItemStyle-BackColor="yellow"
       EditItemStyle-ForeColor="black"
       DataKeyField="CateID"
       OnItemCommand="DoItemSelect"
       OnEditCommand="DoItemEdit"
       OnUpdateCommand="DoItemUpdate"
       OnDeleteCommand="DoItemDelete"
       OnCancelCommand="DoItemCancel">

    <HeaderTemplate>
      <b>Some Minh Khai' s Books:</b><br />
    </HeaderTemplate>

    <ItemTemplate>
      <ASP:Button CommandName="Select" 
	Text="Info" runat="server" />
      <%# Container.DataItem("CateName") %>
    </ItemTemplate>

    <SelectedItemTemplate>
      CateName: <b>
	<%# Container.DataItem("CateName") %></b><br />
      <ASP:Button CommandName="Edit" 
	Text="Edit" runat="server" />
      CateID: <%# Container.DataItem("CateID") %> &nbsp;    
    </SelectedItemTemplate>

    <EditItemTemplate>
      <b>CateID: 
	<%# Container.DataItem("CateID") %></b> &nbsp;
      <ASP:Button CommandName="Update" 
	Text="Update" runat="server" />
      <ASP:Button CommandName="Delete" 
	Text="Delete" runat="server" />
      <ASP:Button CommandName="Cancel" 
	Text="Cancel" runat="server" /><br />
      Name:
      <ASP:TextBox id="txtName" 
	Text='<%# Container.DataItem("CateName") %>'
                   size="46" runat="server" /><br />
      
    </EditItemTemplate>

  </ASP:DataList>

</form>

<script language="vb" runat="server">
Sub Page_Load()
   If Not Page.IsPostback Then
      BindDataGrid() 'create data set and bind to list control
   End If
End Sub


Sub DoItemSelect(objSource As Object, objArgs As DataListCommandEventArgs)

   lblSQL.Text = "" 
   If objArgs.CommandName = "Select" Then
     MyDataList.SelectedIndex = objArgs.Item.ItemIndex
     BindDataGrid()  'bind the data and display it

   End If

End Sub


Sub DoItemEdit(objSource As Object, objArgs As DataListCommandEventArgs)

   'set the SelectedIndex property of the list to -1 to "unselect" it
   MyDataList.SelectedIndex = -1

   'set the EditItemIndex property of the list to this item's index
   MyDataList.EditItemIndex = objArgs.Item.ItemIndex
   BindDataGrid()  'bind the data and display it

End Sub


Sub DoItemUpdate(objSource As Object, objArgs As DataListCommandEventArgs)

   Dim objNameCtrl
   objNameCtrl = CType(objArgs.Item.FindControl("txtName"), TextBox)
 
   Dim strSQL As String
   strSQL = "UPDATE tblCategories SET CateName='" & objNameCtrl.Text & "' " _        
          & "WHERE CateID='" & MyDataList.DataKeys(objArgs.Item.ItemIndex) & "'"
   ExecuteSQLStatement(strSQL)

   'set EditItemIndex property of grid to -1 to switch out of Edit mode
   MyDataList.EditItemIndex = -1
   BindDataGrid()  'bind the data and display it

End Sub

Sub DoItemDelete(objSource As Object, objArgs As DataListCommandEventArgs)

   'create a suitable SQL statement and execute it
   Dim strSQL As String
   strSQL = "DELETE FROM tblCategories WHERE CateID='" _
          & MyDataList.DataKeys(objArgs.Item.ItemIndex) & "'"
   ExecuteSQLStatement(strSQL)

   'set EditItemIndex property of grid to -1 to switch out of Edit mode
   MyDataList.EditItemIndex = -1
   BindDataGrid()  'bind the data and display it

End Sub


Sub DoItemCancel(objSource As Object, objArgs As DataListCommandEventArgs)

   'set EditItemIndex property of grid to -1 to switch out of Edit mode
   MyDataList.EditItemIndex = -1
   BindDataGrid()  'bind the data and display it

End Sub


Sub ExecuteSQLStatement(strSQL)

   'this is where the SQL statement would be executed against the
   'original data source. In this example, we're simply displaying
   'the statement in a Label on the page
   lblSQL.Text = "<b>The SQL statement that would be executed is:</b><br />" & strSQL

End Sub


Sub BindDataGrid()

   Dim strConnect As String  = ConfigurationSettings.appSettings("conn")

   'create a SQL statement to select some rows from the database
   Dim strSelect As String
   strSelect = "SELECT * FROM tblCategories"

   'create a variable to hold an instance of a DataReader object
   Dim objDataReader As SqlDataReader

   Try

      'create a new Connection object using the connection string
      Dim objConnect As New SqlConnection(strConnect)

      'open the connection to the database
      objConnect.Open()

      'create a new Command using the connection object and select statement
      Dim objCommand As New SqlCommand(strSelect, objConnect)

      'execute the SQL statement against the command to get the DataReader
      objDataReader = objCommand.ExecuteReader()

    Catch objError As Exception

       'display error details
       outError.InnerHtml = "<b>* Error while accessing data</b>.<br />" _
           & objError.Message & "<br />" & objError.Source & "<p />"
       Exit Sub  ' and stop execution

    End Try

   'set the DataSource property and bind the list
   MyDataList.DataSource = objDataReader
   MyDataList.DataBind()

End Sub

</script>
</body>
</html>
